package DBModule;

import java.io.IOException;
import java.sql.*;
import java.util.*;
import Test.*;
import Test.Exception.FactoryException;
import Tool.Tool;

public class QuestionTable {
	private static String tableName = "QuestionBody";
	private QuestionTable(){}
	/** 以默认序号插入题干，同时改写ID */
	protected static final DBUpdater<QuestionBody> newInserter = new DBUpdater<QuestionBody>() {
		@Override
		public void update(Connection conn, QuestionBody... qs) throws SQLException,
				IOException {
			PreparedStatement ps = null, ps1 = null;
			try {
				ps = conn.prepareStatement(
						"insert into " + tableName + 
						"(CID, grade, type, TID, data) " +
						"values(?,?,?,?,?)");
				
				ps1 = conn.prepareStatement(
						"select max(QID) from " +
						tableName + " where CID=?");
				
				for (QuestionBody q : qs){
					ps.setInt(1, q.getCourse().getId());
					ps.setInt(2, q.getGrade());
					ps.setInt(3, q.getType());
					ps.setInt(4, q.getTeacherID());
					ps.setBinaryStream(5, Tool.getInputStream(q));
					ps.executeUpdate();
					ps1.setInt(1, q.getCourse().getId());
					ResultSet rs = ps1.executeQuery();
					if (!rs.next()) throw new SQLException("unknow exception when query an exist element");
					q.setId(rs.getInt(1));
				}
			}
			finally{
				if (ps != null) ps.close();
				if (ps1 != null) ps1.close();
			}
		}
	};
	
	protected static final DBUpdater<QuestionBody> inserter = new DBUpdater<QuestionBody>() {
		@Override
		public void update(Connection conn, QuestionBody... qs) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(
						"insert into " + tableName + 
						"(QID, CID, grade, type, TID, data) " +
						"values(?,?,?,?,?,?)");
				for (QuestionBody q : qs){
					ps.setInt(1, q.getId());
					ps.setInt(2, q.getCourse().getId());
					ps.setInt(3, q.getGrade());
					ps.setInt(4, q.getType());
					ps.setInt(5, q.getTeacherID());
					ps.setBinaryStream(6, Tool.getInputStream(q));
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	protected static final DBUpdater<QuestionBody> updater = new DBUpdater<QuestionBody>() {
		@Override
		public void update(Connection conn, QuestionBody... qs) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(
						"update " + tableName +
						" set grade=?, type=?, " +
						"TID=?, data=? where QID=? and CID=?");
				for (QuestionBody q : qs){
					ps.setInt(1, q.getGrade());
					ps.setInt(2, q.getType());
					ps.setInt(3, q.getTeacherID());
					ps.setBinaryStream(4, Tool.getInputStream(q));
					ps.setInt(5, q.getId());
					ps.setInt(6, q.getCourse().getId());
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	protected static final DBUpdater<QuestionBody> deleter = new DBUpdater<QuestionBody>() {
		@Override
		public void update(Connection conn, QuestionBody... qs) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try{
				ps = conn.prepareStatement(
						"delete from " + tableName + " where QID=? and CID=?");
				for (QuestionBody q : qs){
					ps.setInt(1, q.getId());
					ps.setInt(2, q.getCourse().getId());
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	protected static final DBQuerier<Question> batchQuerier = new DBQuerier<Question>() {
		@Override
		/**
		 * keys[0]: Iterator<Integer> IDs
		 * keys[1]: Course 
		 */
		public Collection<Question> query(Connection conn, Object... keys)
				throws SQLException, IOException, FactoryException{
			@SuppressWarnings("unchecked")
			Iterator<Integer> iter = (Iterator<Integer>)keys[0];
			Course course = (Course)keys[1];
			ArrayList<Question> ret = new ArrayList<Question>();
			PreparedStatement ps = null;			
			try{
				ps = conn.prepareStatement(
						"select type, grade, TID, data from " + 
						tableName + " where QID=? and CID=?");
				ps.setInt(2, course.getId());
				
				while (iter.hasNext()){
					int id = iter.next();
					ps.setInt(1, id);
					ResultSet rs = ps.executeQuery();
					while (rs.next()){
						Question q = Question.factory.createObject(rs.getInt(1));
						QuestionBody body = q.getBody();
						body.setId(id);
						body.setCourse(course);
						body.setGrade(rs.getInt(2));
						body.setTeacherID(rs.getInt(3));
						body.fromBytes(rs.getBinaryStream(4));
						ret.add(q);
					}
				}				
			}
			finally{
				if (ps != null) ps.close();
			}
			return ret;
		}
	};
	
	protected static final DBQuerier<Question> querier = new DBQuerier<Question>() {
		@Override
		/**
		 * keys[0]: Question ID
		 * keys[1]: Course 
		 */
		public Collection<Question> query(Connection conn, Object... keys)
				throws SQLException, IOException, FactoryException{
			final int id = (Integer)keys[0];	// 解包
			Iterator<Integer> iter = new Iterator<Integer>() {
				boolean flag = true;
				public void remove() {}
				public Integer next() { flag = false; return id; }
				public boolean hasNext() {return flag;}
			};
			return batchQuerier.query(conn, iter, keys[1]);
		}
	};
	
	/**
	 *  将题干以默认ID插入数据库 
	 *  同时修改题干的ID 
	 * @throws Exception 
	 */
	public static void insertAsDefaultID(QuestionBody...questions) throws Exception{
		DBManager.update(newInserter, questions);
	}
	
	/** 将题干插入数据库 
	 * @throws Exception */
	public static void insert(QuestionBody...questions) throws Exception{
		DBManager.update(inserter, questions);
	}
	
	/** 更新题干 
	 * @throws Exception 
	 */
	public static void update(QuestionBody...questions) throws Exception{
		DBManager.update(updater, questions);
	}
	
	/** 删除题干 
	 * @throws Exception 
	 */
	public static void delete(QuestionBody...questions) throws Exception{
		DBManager.update(deleter, questions);
	}
	
	/** 得到某一科目所有指定ID的题目
	 * @throws FactoryException 
	 * @throws IOException 
	 * @throws SQLException 
	 *   
	 */
	public static Collection<Question> getQuestion(Course course, Iterator<Integer> ids) throws SQLException, IOException, FactoryException{
		return DBManager.query(batchQuerier, ids, course);
	}
	
	/** 得到某一科目指定ID的题目，失败返回null */
	public static Question getQuestion(Course course, int id){
		Iterator<Question> iter;
		try {
			iter = DBManager.query(querier, id, course).iterator();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return iter != null && iter.hasNext() ? iter.next() : null;
	}
}






